﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class BudgetManagementTestBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void Insert(int MngSeq, string TestName, string Remarks, int Confirm, int CreatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@mng_seq", MngSeq);
                dbManager.AddParameters(1, "@mng_test_name", TestName);
                dbManager.AddParameters(2, "@remarks", Remarks);
                dbManager.AddParameters(3, "@confirm", Confirm);
                dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@created_by", CreatedBy);
                dbManager.AddParameters(6, "@created_when", DateTime.Now);

                string strSQL;

                strSQL = " INSERT INTO HRM_PRM_MANAGEMENT_TEST"
                        + " (MNG_SEQ, MNG_TEST_NAME, CONFIRM, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        + " VALUES(@mng_seq, @mng_test_name, @confirm, @remarks, @record_status, @created_by, @created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw new Exception("{Insert_hrm_prm_management_test}" + ex.Message);
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void Update(int Sequence, string TestName, string Remarks, int Confirm, int updated_by)
        {

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@mng_test_name", TestName);
                dbManager.AddParameters(2, "@remarks", Remarks);
                dbManager.AddParameters(3, "@confirm", Confirm);
                dbManager.AddParameters(4, "@updated_by", updated_by);
                dbManager.AddParameters(5, "@updated_when", DateTime.Today);

                string sql;
                sql = " UPDATE HRM_PRM_MANAGEMENT_TEST"
                    + " SET MNG_TEST_NAME = @mng_test_name"
                        + ", REMARKS = @remarks"
                        + ", CONFIRM =@confirm "
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE MNG_TEST_SEQ = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text,sql); 
             }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();  
            }
        
        
        }

        public void Delete(int seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", UpdatedBy);
                dbManager.AddParameters(2, "@seq", seq);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string sql;
                sql = " UPDATE HRM_PRM_MANAGEMENT_TEST SET "
                    + " RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE MNG_TEST_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                
                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void InsertManagement(int PrmSeq, int JobSiteSeq, double MngPercent, double Amount, double AmountNet, int CreatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@prm_seq", PrmSeq);
                dbManager.AddParameters(2, "@job_site_seq", JobSiteSeq);
                dbManager.AddParameters(3, "@mng_percent", MngPercent);
                dbManager.AddParameters(4, "@amount", Amount);
                dbManager.AddParameters(5, "@amount_net", AmountNet);
                dbManager.AddParameters(6, "@created_by", CreatedBy);
                dbManager.AddParameters(7, "@created_when", DateTime.Now);
                
                strSQL = " INSERT INTO HRM_PRM_MANAGEMENTS"
                        + "(PRM_SEQ, JOB_SITE_SEQ, BUDGET_PERCENT, AMOUNT, AMOUNT_NET, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@prm_seq, @job_site_seq, @mng_percent, @amount, @amount_net, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void UpdateManagement(int Sequence, double MngPercent, double Amount, double AmountNet, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@mng_percent", MngPercent);
                dbManager.AddParameters(3, "@amount", Amount);
                dbManager.AddParameters(4, "@amount_net", AmountNet);
                dbManager.AddParameters(5, "@updated_by", UpdatedBy);
                dbManager.AddParameters(6, "@updated_when", DateTime.Now);

                string sql;
                sql = " update hrm_prm_managements set "
                        + " budget_percent = @mng_percent"
                        + ", amount = @amount"
                        + ", amount_net = @amount_net"
                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                    + " where mng_seq = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable Management(int Year, int Period, int EmployeeType)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@year", Year);
                dbManager.AddParameters(2, "@period", Period);
                dbManager.AddParameters(3, "@emp_type_seq", EmployeeType);

                DataSet ds = null;
                DataTable dt = null;

                string sql = " SELECT js.JOB_SITE_SEQ, js.JOB_SITE_NAME, m.BUDGET_PERCENT"
                        + ", m.AMOUNT, m.MNG_SEQ, m.AMOUNT_NET, 50000 AS SUM_SALARY"
                    + " FROM HRM_MP_JOB_SITES js"
                    + " LEFT JOIN HRM_PRM_MANAGEMENTS m ON js.JOB_SITE_SEQ = m.JOB_SITE_SEQ"
                        + " AND m.PRM_SEQ = (select prm_seq from hrm_prm_promotions"
                            + " where prm_year = @year"
                            + " and prm_period = @period"
                            + " and record_status = @ActiveRecord)"
                    //+ " LEFT JOIN ( select salary, job_site_seq " //หาเงืนเดือน ณ วันที่คิดโควตาของการเลื่อนเงินเดือน
                    //        + " from hrm_ps_emp_salary_histories h"
                    //        + " where max(start_date) <= (select prm_date "
                    //                                + " from hrm_prm_promotions"
                    //                                + " where record_status = @ActiveRecord"
                    //                                + " and prm_year = @year"
                    //                                + " and prm_period = @period"
                    //                                + " and emp_type_seq = @emp_type_seq"
                    //                                + ")"
                    //        + " group by emp_seq"
                    //        + ")sal ON js.JOB_SITE_SEQ = sal.JOB_SITE_SEQ"
                    + " WHERE js.RECORD_STATUS = @ActiveRecord"
                        + " AND js.upper_job_site_seq IS NOT NULL"
                        + " AND js.JOB_SITE_LEVEL_SEQ = 3" // ระดับสำนัก/กอง
                        + " AND js.JOB_SITE_TYPE_SEQ = 2"  // โครงสร้างภายใน
                        + " AND js.upper_job_site_seq = (select curr_job_site_seq "
                                                + " from hrm_mp_job_site_types"
                                                + " where job_site_type_seq = 2)"
                       // + " GROUP BY js.JOB_SITE_SEQ"
                        + " ORDER BY js.JOB_SITE_REF";
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0].Copy();

                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public int GetSumBudget(int JobSiteSeq, int EmpTypeSeq, int Year, int Period)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Year", Year);
                dbManager.AddParameters(2, "@Period", Period);
                dbManager.AddParameters(3, "@JobSiteSeq", "%/" + JobSiteSeq.ToString() + "/%");
                dbManager.AddParameters(4, "@EmpTypeSeq", EmpTypeSeq);

                string strSQL = "SELECT SUM(s.NEW_SALARY) AS SUM_SALARY_QUOTA "
                    + " FROM("
                    + " SELECT d.NEW_SALARY, pe.PRM_JOB_SITE_SEQ AS JOB_SITE_SEQ"
                    //+ ", CASE WHEN e.WORKING_JOB_SITE_SEQ IS NULL THEN p.JOB_SITE_SEQ"
                    //    + " ELSE e.WORKING_JOB_SITE_SEQ END AS JOB_SITE_SEQ"
                    + " FROM HRM_PRM_PROMOTION_EMPLOYEES pe"
                    //+ " FROM HRM_PS_EMPLOYEES e"
                    + " LEFT JOIN HRM_PS_COMMAND_DTLS d ON pe.EMP_SEQ = d.EMP_SEQ"
                    //+ " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " WHERE d.RECORD_STATUS = @record_status"
                        //+ " AND e.WORKING_JOB_SITE_SEQ LIKE @JobSiteSeq"
                        + " AND d.START_DATE = (select max(start_date) from hrm_ps_command_dtls "
                                    + " where emp_seq = d.emp_seq"
                                    + " and record_status = @record_status"
                                    + " and new_salary is not null"
                                    + " and start_date <= (select prm_date from hrm_prm_promotions"
                                        + " where emp_type_seq = @EmpTypeSeq"
                                        + " and prm_year = @Year"
                                        + " and prm_period = @Period"
                                        + ")"
                                    + ")"
                     + ")s" 
                     + " LEFT JOIN HRM_MP_JOB_SITES js ON s.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                     + " WHERE js.JOB_SITE_FULL_PATH LIKE @JobSiteSeq"
                        + " GROUP BY js.JOB_SITE_FULL_PATH";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["SUM_SALARY_QUOTA"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable select(Nullable<int> Sequence)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@mng_seq", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                string sql = " SELECT " +
                            " hrm_prm_managements.mng_seq, " +
                            " hrm_prm_managements.mng_name, " +
                            " hrm_apr_appraisals.apr_year, " +
                            " hrm_apr_appraisals.apr_period, " +
                            " hrm_prm_managements.amount, " +
                            " hrm_prm_managements.confirm, " +
                            " 0 as apr_over, " +
                            " hrm_prm_managements.prm_seq " +
                            " FROM " +
                            " hrm_prm_managements " +
                            " left Join hrm_prm_promotions ON hrm_prm_managements.prm_seq = hrm_prm_promotions.prm_seq " +
                            " left Join hrm_apr_appraisals ON hrm_apr_appraisals.apr_seq = hrm_prm_promotions.apr_seq " +
                            " WHERE hrm_prm_managements.record_status not like @record_status" 
                            + (Sequence.HasValue ? " AND hrm_prm_managements.mng_seq = @mng_seq" : "");
                return dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable select(int JobSiteSeq,int EmpTypeSeq,int PrmYear,int PrmPeriod,int AprPeriod)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@job_site_seq", JobSiteSeq);
                dbManager.AddParameters(1, "@emp_type_seq", EmpTypeSeq);
                dbManager.AddParameters(2, "@prm_year", PrmYear);
                dbManager.AddParameters(3, "@prm_period", PrmPeriod);
                dbManager.AddParameters(4, "@apr_period", AprPeriod);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);
                string sql = " SELECT t.*, m.*, SUM(sub.AMOUNT) AS SUM_AMOUNT"
                        + ", a.APR_YEAR, a.APR_PERIOD"
                    + " FROM HRM_PRM_MANAGEMENT_TEST t"
                    + " LEFT JOIN HRM_PRM_MANAGEMENTS m ON t.MNG_SEQ = m.MNG_SEQ"
                    + " LEFT JOIN HRM_PRM_PROMOTIONS pr ON m.PRM_SEQ = pr.PRM_SEQ"
                    + " LEFT JOIN HRM_APR_APPRAISALS a ON pr.APR_SEQ = a.APR_SEQ"
                    + " LEFT JOIN (select amount, prm_seq, prm_job_site_seq"
                                + " from hrm_prm_promotion_employees"
                                + " where record_status = @record_status"
                                + ")sub ON m.PRM_SEQ = sub.PRM_SEQ AND m.JOB_SITE_SEQ = sub.PRM_JOB_SITE_SEQ"
                    + " WHERE t.RECORD_STATUS = @record_status"
                        + " /*AND m.JOB_SITE_SEQ = @job_site_seq"
                        + " AND pr.PRM_YEAR = @prm_year"
						+ " AND pr.PRM_PERIOD = @prm_period*/"
                        + " GROUP BY m.JOB_SITE_SEQ, t.MNG_TEST_SEQ";
                return dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable SelectManageSummary(int EmpTypeSeq, int PrmYear, int PrmPeriod)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@emp_type_seq", EmpTypeSeq);
                dbManager.AddParameters(1, "@prm_year", PrmYear);
                dbManager.AddParameters(2, "@prm_period", PrmPeriod);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);

                string sql = " SELECT t.*, m.*, SUM(sub.AMOUNT) AS SUM_AMOUNT"
                        + ", a.APR_YEAR, a.APR_PERIOD, j.JOB_SITE_NAME"
                        + ", (m.AMOUNT_NET - SUM(sub.AMOUNT)) AS TOTAL"
                    + " FROM HRM_PRM_MANAGEMENT_TEST t"
                    + " LEFT JOIN HRM_PRM_MANAGEMENTS m ON t.MNG_SEQ = m.MNG_SEQ"
                    + " LEFT JOIN HRM_PRM_PROMOTIONS pr ON m.PRM_SEQ = pr.PRM_SEQ"
                    + " LEFT JOIN HRM_APR_APPRAISALS a ON pr.APR_SEQ = a.APR_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON m.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN (select amount, prm_seq, prm_job_site_seq"
                                + " from hrm_prm_promotion_employees"
                                + " where record_status = @record_status"
                                + ")sub ON m.PRM_SEQ = sub.PRM_SEQ AND m.JOB_SITE_SEQ = sub.PRM_JOB_SITE_SEQ"
                    + " WHERE t.RECORD_STATUS = @record_status"
                        + " AND pr.PRM_YEAR = @prm_year"
                        + " AND pr.PRM_PERIOD = @prm_period"
                        + " AND t.CONFIRM = 1"
                        + " GROUP BY m.JOB_SITE_SEQ, t.MNG_TEST_SEQ";
                return dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public string GetDepartName(int EmpSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@emp_seq", EmpSeq);
                string sql;
                sql = " SELECT hrm_mp_job_sites2.job_site_name " +
                        " FROM (SELECT " +
                        " 	ifNULL(hrm_ps_employees.working_job_site_seq,  " +
                        " 	hrm_mp_positions.job_site_seq) as working_job_site_seq  " +
                        " 	FROM  " +
                        " 	hrm_ps_employees  " +
                        " 	Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq " +
                        " 	WHERE hrm_ps_employees.emp_seq = @emp_seq) as T1  " +
                        " 	Inner Join hrm_mp_job_sites ON T1.working_job_site_seq = hrm_mp_job_sites.job_site_seq " +
                        " 	Inner Join hrm_mp_job_sites AS hrm_mp_job_sites2 ON hrm_mp_job_sites.upper_job_site_seq = hrm_mp_job_sites2.job_site_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return ds.Tables[0].Rows[0]["job_site_name"].ToString();
            }
            catch
            {
                return "";
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetEmployeeTypes()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = "SELECT EMP_TYPE_SEQ, EMP_TYPE_NAME, REMARKS, CREATED_BY, UPDATED_BY"
                    + " FROM HRM_PS_EMPLOYEE_TYPES"
                    + " WHERE RECORD_STATUS not like @DeleteRecord";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public string GetYearTypeName(int yearTypeSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@year_type_seq", yearTypeSeq);
                string sql;
                sql = " SELECT " +
                        " sys_year_type.year_type_name " +
                        " FROM " +
                        " sys_year_type " +
                        " Inner Join hrm_ps_employee_types ON hrm_ps_employee_types.year_type_seq = sys_year_type.year_type_seq " +
                        " WHERE " +
                        " hrm_ps_employee_types.emp_type_seq = @year_type_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return ds.Tables[0].Rows[0]["year_type_name"].ToString();
            }
            catch
            {
                return "";
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetAppraisals(int Year)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@apr_year", Year);

                //string strSQL = " SELECT " +
                //                " hrm_apr_appraisals.apr_period, " +
                //                " concat(hrm_apr_appraisals.apr_name,'  ', " +
                //                " cast(hrm_apr_appraisals.act_start_date as char),' ถึง ', " +
                //                " cast(hrm_apr_appraisals.act_end_date as char)) as apr_name " +
                //                " FROM " +
                //                " hrm_apr_appraisals " +
                //                " WHERE " +
                //                " hrm_apr_appraisals.apr_year =  @apr_year " +
                //                " Order By apr_period";
                string strSQL = " SELECT " +
                                " hrm_apr_appraisals.apr_period, " +
                                " hrm_apr_appraisals.apr_name, " +
                                " hrm_apr_appraisals.act_start_date, " +
                                " hrm_apr_appraisals.act_end_date " +
                                " FROM  " +
                                " hrm_apr_appraisals  " +
                                " WHERE  " +
                                " hrm_apr_appraisals.apr_year =  @apr_year " +
                                " Order By apr_period";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public int GetManageSeq(int JobSiteSeq, int PromotionSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@JobSiteSeq", JobSiteSeq);
                dbManager.AddParameters(1, "@PromotionSeq", PromotionSeq);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                string sql;
                sql = " SELECT MNG_SEQ FROM HRM_PRM_MANAGEMENTS"
                        + " WHERE JOB_SITE_SEQ = @JobSiteSeq"
                        + " AND PRM_SEQ = @PromotionSeq"
                        + " AND RECORD_STATUS = @RecordStatus";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["MNG_SEQ"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable GetPromotions(int Year)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@prm_year", Year);
                string strSQL = " SELECT " +
                                " hrm_prm_promotions.prm_period, " +
                                " hrm_prm_promotions.prm_name " +
                                " FROM " +
                                " hrm_prm_promotions " +
                                " WHERE " +
                                " hrm_prm_promotions.prm_year =  @prm_year " +
                                " Order By hrm_prm_promotions.prm_period";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetJobSiteSeq(int EmpSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@emp_seq", EmpSeq);
                string sql;
                sql = " SELECT " +
                        " ifNULL(hrm_ps_employees.working_job_site_seq, " +
                        " hrm_mp_positions.job_site_seq) as working_job_site_seq " +
                        " FROM " +
                        " hrm_ps_employees " +
                        " Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq " +
                        " WHERE " +
                        " hrm_ps_employees.emp_seq = @emp_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["working_job_site_seq"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetPrmPromotionSeq(int EmpTypeSeq, int PrmYear, int PrmPriod)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@emp_type_seq", EmpTypeSeq);
                dbManager.AddParameters(1, "@prm_year", PrmYear);
                dbManager.AddParameters(2, "@prm_period", PrmPriod);
                string sql;
                sql = " SELECT " +
                        " hrm_prm_promotions.prm_seq " +
                        " FROM " +
                        " hrm_prm_promotions " +
                        " WHERE " +
                        " hrm_prm_promotions.emp_type_seq =  @emp_type_seq AND " +
                        " hrm_prm_promotions.prm_year =  @prm_year AND " +
                        " hrm_prm_promotions.prm_period =  @prm_period ";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["prm_seq"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetAprAppraisalsSeq(int EmpTypeSeq, int PrmYear, int PrmPriod)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@emp_type_seq", EmpTypeSeq);
                dbManager.AddParameters(1, "@apr_year", PrmYear);
                dbManager.AddParameters(2, "@apr_period", PrmPriod);
                string sql;
                sql = " SELECT " +
                        " apr_seq " +
                        " FROM " +
                        " hrm_apr_appraisals " +
                        " WHERE " +
                        " apr_year =  @apr_year AND " +
                        " apr_period =  @apr_period AND " +
                        " emp_type_seq =  @emp_type_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["apr_seq"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public int GetPromotionQuota(int JobSiteSeq, DateTime SalaryDate, double Percent)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@JobSiteSeq", "%/" + JobSiteSeq.ToString() + "/%");
                dbManager.AddParameters(2, "@SalaryDate", SalaryDate);
                dbManager.AddParameters(3, "@Percent", Percent); 
                string strSQL;
                strSQL = " select sum(sc.new_salary) as sum_salary_quota"//, e.emp_seq, sc.start_date"
                    + " from hrm_ps_employees e"
                    + " LEFT JOIN hrm_mp_positions p on e.emp_pos_seq = p.emp_pos_seq"
                    + " LEFT JOIN hrm_mp_job_sites wj on e.working_job_site = wj.job_site_seq"
                    + " LEFT JOIN hrm_mp_job_sites js on p.job_site_seq = js.job_site_seq"
                    + " LEFT JOIN (select new_salary , start_date, emp_seq "
                        + " from hrm_ps_command_dtls d"
                        + " where start_date = (select max(start_date) from hrm_ps_command_dtls"
										+ " where emp_seq = d.emp_seq "
                                        + " and (new_salary is not null and new_salary > 0)"
										+ " and start_date <=getdate())"
						+ " )sc on e.emp_seq = sc.emp_seq"
	                    + " WHERE e.RECORD_STATUS = @RecordStatus"
                        + " AND CASE WHEN wj.full_path is not null then wj.full_path like @JobSiteSeq"
                            + " else js.full_path like @JobSiteSeq";

                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["sum_salary_quota"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
